﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Text;


namespace Wicresoft.Shell.Package.AppExp
{
    static class MySqlHelper
    {
        private static readonly string MYSQL_PROVIDER = "MySql.Data.MySqlClient";

        public static String BuildConnectionStr(String dataSrc, String dbStr, String userId, String pwdStr)
        {
            Debug.Assert(!String.IsNullOrEmpty(dataSrc), "dataSrc must be provided.");
            Debug.Assert(!String.IsNullOrEmpty(userId), "userId must be provided.");

            StringBuilder mySqlCnnStrB = new StringBuilder();

            mySqlCnnStrB.AppendFormat("Data Source={0};User Id={1}", dataSrc, userId);
            if (!String.IsNullOrEmpty(pwdStr))
                mySqlCnnStrB.AppendFormat(";Password={0}", pwdStr);
            if (!String.IsNullOrEmpty(dbStr))
                mySqlCnnStrB.AppendFormat(";database={0}", dbStr);
            mySqlCnnStrB.Append(";Connect Timeout=5000");
            return mySqlCnnStrB.ToString();
        }

        //public static String BuildConnectionStr(String dataSrc, String dbStr, String userId, String pwdStr)
        //{
        //    Debug.Assert(!String.IsNullOrEmpty(dataSrc), "dataSrc must be provided.");
        //    //Debug.Assert(!String.IsNullOrEmpty(dbStr), "dbStr must be provided.");
        //    Debug.Assert(!String.IsNullOrEmpty(userId), "userId must be provided.");
        //    //Debug.Assert(!String.IsNullOrEmpty(pwdStr), "userId must be provided.");

        //    String cnnStrFormat = "metadata=res://*/Mobile.csdl|res://*/Mobile.ssdl|res://*/Mobile.msl;provider=MySql.Data.MySqlClient;provider connection string=\"server={0};User Id={2};database={1};password={3};Persist Security Info=True;Character Set=gb2312;Connect Timeout =5000\"";

        //    return String.Format(cnnStrFormat, dataSrc, dbStr, userId, pwdStr);
        //}



        public static Boolean GetDBNames(String cnnStr, out String[] dbNames, out String errInfo)
        {
            Boolean state = true;

            DbProviderFactory factory = DbProviderFactories.GetFactory(MYSQL_PROVIDER);
            DbConnection mySqlCnn = null;
            DbCommand mySqlCmd = null;
            DbDataReader mySqlReader = null;
            List<String> dbNameList = new List<String>();

            errInfo = String.Empty;
            try
            {
                mySqlCnn = factory.CreateConnection();
                mySqlCnn.ConnectionString = cnnStr;
                mySqlCnn.Open();
                mySqlCmd = mySqlCnn.CreateCommand();
                mySqlCmd.CommandText = "show databases";
                mySqlCmd.Connection = mySqlCnn;
                mySqlReader = mySqlCmd.ExecuteReader();
                while (mySqlReader.Read())
                    dbNameList.Add(mySqlReader.GetString(0));
            }
            catch (Exception ex)
            {
                state = false;
                errInfo = ex.Message;
            }
            finally
            {
                if (null != mySqlReader && !mySqlReader.IsClosed)
                    mySqlReader.Close();
                if (null != mySqlCnn && mySqlCnn.State == System.Data.ConnectionState.Open)
                    mySqlCnn.Close();
            }
            dbNames = dbNameList.ToArray();

            return state;
        }

        public static Boolean GetDBNames(String dataSrc, String userId, String pwdStr, out String[] dbNames, out String errInfo)
        {
            String cnnStr = BuildConnectionStr(dataSrc, String.Empty, userId, pwdStr);

            return GetDBNames(cnnStr, out dbNames, out errInfo);
        }

        public static Boolean TestDBConnection(String cnnStr, out String errInfo)
        {
            Boolean state = true;
            DbProviderFactory factory = DbProviderFactories.GetFactory(MYSQL_PROVIDER);
            DbConnection connection = null;

            errInfo = String.Empty;
            try
            {
                connection = factory.CreateConnection();
                connection.ConnectionString = cnnStr;
                connection.Open();
            }
            catch (Exception ex)
            {
                state = false;
                errInfo = ex.Message;
            }
            finally
            {
                if (null != connection && connection.State == ConnectionState.Open)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return state;
        }

        public static Boolean TestDBConnection(String dataSrc, String dbStr, String userId, String pwdStr, out String errInfo)
        {
            String cnnStr = BuildConnectionStr(dataSrc, dbStr, userId, pwdStr);

            return TestDBConnection(cnnStr, out errInfo);
        }
    }
}
